/*
 * MIT License
 *
 * Copyright (c) 2023 北京凯特伟业科技有限公司
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */
package com.je.common.base.office.service;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.WorkbookUtil;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.je.common.base.DynaBean;
import com.je.common.base.document.InternalFileBO;
import com.je.common.base.office.util.OfficeUtil;
import com.je.common.base.office.vo.JeExcelVo;
import com.je.common.base.office.vo.JeOfficeVo;
import com.je.common.base.office.vo.OfficeDataVo;
import com.je.common.base.service.rpc.DocumentInternalRpcService;
import com.je.common.base.util.*;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.util.*;

@Component("jeExcelService")
public class JeExcelServiceImpl implements JeExcelService {
//    @Autowired
//    private DocumentBusService documentBusService;
    @Autowired
    private DocumentInternalRpcService documentInternalRpcService;
    @Autowired
    private JeOfficeService jeOfficeService;
    @Override
    public void doExecOffice(JeOfficeVo jeOfficeVo, JSONObject returnObj) {
        String fileKey=OfficeUtil.getFileKey(jeOfficeVo.getFile());
        if(StringUtil.isEmpty(fileKey)){
            returnObj.put("success",false);
            returnObj.put("obj","模版文件未上传");
        }
//        JSONObject.parseObject(fileStr).getString("fileKey");
        JeExcelVo jeExcelVo=new JeExcelVo();
        InternalFileBO fileBO=documentInternalRpcService.selectFileByKey(fileKey);
        File file=documentInternalRpcService.readFile(fileKey);
        FileInputStream fileIo=null;
        jeOfficeVo.setFileContextType(fileBO.getContentType());
        DynaBean docConfig=jeOfficeVo.getDocConfig();
        if(!file.exists()){
            returnObj.put("success",false);
            returnObj.put("obj","模版文件不存在!");
            return;
        }
        try{
            fileIo=new FileInputStream(file);
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            ExcelWriter excelWriter=new ExcelWriter(WorkbookUtil.createBook(fileIo, false),null);
            Workbook workbook = excelWriter.getWorkbook();
            //执行事件
            jeExcelVo.setWorkbook(workbook);
            jeOfficeVo.setJeExcelVo(jeExcelVo);
            //执行事件
            //执行事件
            jeOfficeService.doExecEvent(jeOfficeVo,"before");
            int maxColumn=50;
            if(StringUtil.isNotEmpty(docConfig.getStr("EXCEL_MAXCOLUMN"))){
                maxColumn=docConfig.getInt("EXCEL_MAXCOLUMN");
            }
            for(int sheetIndex=0;sheetIndex<workbook.getNumberOfSheets();sheetIndex++){
                Sheet sheet=workbook.getSheetAt(sheetIndex);
                int endRow=sheet.getLastRowNum();
                for(int nowRowIndex=0;nowRowIndex<=endRow;nowRowIndex++){
                    Row nowRow=sheet.getRow(nowRowIndex);
                    if(nowRow==null)continue;
                    Set<String> moreRows=new HashSet<>();
                    for(int nowColumnIndex=0;nowColumnIndex<maxColumn;nowColumnIndex++){
                        Cell nowCell=nowRow.getCell(nowColumnIndex);
                        if(nowCell==null){
                            continue;
                        }
                        String cellValue=getCellStringValue(nowCell);
                        if(StringUtil.isEmpty(cellValue))continue;
                        if(cellValue.startsWith("{") && cellValue.endsWith("}")){
                            String fieldStr=cellValue.substring(1,cellValue.length()-1);
                            String dsCode="";
                            String fieldCode="";
                            if(fieldStr.split("\\.").length==2){
                                dsCode=fieldStr.split("\\.")[0];
                                fieldCode=fieldStr.split("\\.")[1];
                            }else if(jeOfficeVo.getOfficeDatas().size()==1){
                                dsCode=jeOfficeVo.getOfficeDatas().get(0).getCode();
                                fieldCode=fieldStr;
                            }
                            OfficeDataVo officeDataVo=jeOfficeVo.getOfficeDataVo(dsCode);
                            DynaBean fieldInfo=officeDataVo.getField(fieldCode);
                            if("bean".equals(officeDataVo.getType())){
//                                value=officeDataVo.getDynaBean().getStr(fieldCode,"");
//                                if(StringUtil.isNotEmpty(fieldInfo.getStr("FIELD_VALUE","")) && StringUtil.isEmpty(value)){
//                                    value=fieldInfo.getStr("FIELD_VALUE");
//                                }
                                doSetCellValue(officeDataVo.getDynaBean(),nowCell,fieldInfo,jeOfficeVo);
                            }else{
                                List<DynaBean> lists=officeDataVo.getLists();
                                String fieldKey=dsCode+"."+fieldCode;
                                if(lists.size()<=0){
                                    doSetCellValue(new DynaBean(),nowCell,fieldInfo,jeOfficeVo);
                                }
                                for(int i=0;i<lists.size();i++){
                                    DynaBean bean=lists.get(i);
//                                    value=bean.getStr(fieldCode,"");
//                                    if(StringUtil.isNotEmpty(fieldInfo.getStr("FIELD_VALUE","")) && StringUtil.isEmpty(value)){
//                                        value=fieldInfo.getStr("FIELD_VALUE");
//                                    }
                                    if(i>0){
                                        if("1".equals(fieldInfo.getStr("EXCEL_ROWDATA"))){
                                            if(sheet.getLastRowNum()>=(nowRowIndex + i)) {
                                                sheet.shiftRows(nowRowIndex + i, sheet.getLastRowNum(), 1);
                                            }
                                            Row dataRow=sheet.createRow(nowRowIndex+i);
                                            endRow++;
                                            dataRow.setRowStyle(nowRow.getRowStyle());
                                            dataRow.setHeight(nowRow.getHeight());
                                            Cell dataCell=dataRow.createCell(nowColumnIndex);
                                            dataCell.setCellStyle(nowCell.getCellStyle());
                                            doSetCellValue(bean,dataCell,fieldInfo,jeOfficeVo);
                                        }else if(moreRows.contains(dsCode+"."+fieldInfo.getStr("EXCEL_ROWFIELD"))){
                                            Row dataRow=sheet.getRow(nowRowIndex+i);
                                            if(dataRow==null)continue;
                                            Cell dataCell=dataRow.getCell(nowColumnIndex);
                                            if(dataCell==null){
                                                dataCell=dataRow.createCell(nowColumnIndex);
                                                dataCell.setCellStyle(nowCell.getCellStyle());
                                            }
                                            doSetCellValue(bean,dataCell,fieldInfo,jeOfficeVo);
                                        }
                                    }else{
                                        doSetCellValue(bean,nowCell,fieldInfo,jeOfficeVo);
                                    }
                                }
                                if("1".equals(fieldInfo.getStr("EXCEL_ROWDATA"))){
                                    moreRows.add(fieldKey);
                                }
                            }

                        }
                    }
                }
            }
            jeOfficeVo.setJeExcelVo(jeExcelVo);
            //执行事件
            jeOfficeService.doExecEvent(jeOfficeVo,"after");
            workbook.write(outputStream);
            workbook.close();
            excelWriter.close();
            //输出转输入
            byte[] filebytes=outputStream.toByteArray();
            jeOfficeVo.setFileSize(new Long(filebytes.length));
            ByteArrayInputStream inputStream = IoUtil.toStream(filebytes);
            //关闭ExcelWriter outputStream
            IoUtil.close(outputStream);
            IoUtil.close(fileIo);
            jeOfficeVo.setInputStream(inputStream);
            //如果转pdf
            if(jeOfficeVo.getConvertPdf()){
                byte[] pdfbytes= AsposeUtils.excelToPdf(filebytes);
                if(pdfbytes!=null){
                    jeOfficeVo.setPreviewFileSize(new Long(pdfbytes.length));
                    jeOfficeVo.setPreviewSuffix("pdf");
                    jeOfficeVo.setPreviewContextType("application/pdf");
                    jeOfficeVo.setPreviewStream(IoUtil.toStream(pdfbytes));
                }
            }
            returnObj.put("success",true);
            returnObj.put("obj","操作成功!");
        }catch (Exception e){
            e.printStackTrace();
            returnObj.put("success",false);
            returnObj.put("obj","系统导出出错了，请联系管理员!");
        }
    }
    public void doSetCellValue(DynaBean dynaBean, Cell nowCell, DynaBean fieldInfo, JeOfficeVo jeOfficeVo){
        String fieldCode=fieldInfo.getStr("FIELD_CODE");
        String fieldType=fieldInfo.getStr("FIELD_TYPE");
        String excelType=fieldInfo.getStr("EXCEL_TYPE");
        String configInfo=fieldInfo.getStr("FIELD_CONFIGINFO");
        String valueConfig=fieldInfo.getStr("FIELD_VALCONFIG");
        String value=dynaBean.getStr(fieldCode);
        if("DATE".equals(fieldType)){
            if(StringUtil.isNotEmpty(fieldInfo.getStr("FIELD_CONFIGINFO")) && StringUtil.isNotEmpty(value)){
                Date d=DateUtils.getDate(value,DateUtils.DAFAULT_DATE_FORMAT);
                if(d!=null) {
                    value = DateUtils.formatDate(d, fieldInfo.getStr("FIELD_CONFIGINFO"));
                }
            }
        }else if("DATETIME".equals(fieldType)){
            if(StringUtil.isNotEmpty(fieldInfo.getStr("FIELD_CONFIGINFO")) && StringUtil.isNotEmpty(value)){
                Date d=DateUtils.getDate(value,DateUtils.DAFAULT_DATETIME_FORMAT);
                if(d!=null) {
                    value = DateUtils.formatDate(d, fieldInfo.getStr("FIELD_CONFIGINFO"));
                }
            }
        }else if("MOREFIELD".equals(fieldType)){
            Set<Map.Entry<String,String>> ddSet=new HashSet<>();
            ddSet.addAll(OfficeUtil.getDynaMap(dynaBean).entrySet());
            value= StringUtil.parseKeyWord(valueConfig,ddSet);
        }else if("WFDATA".equals(fieldType)){
            value=dynaBean.getStr("WF_"+fieldInfo.getStr("FIELD_CONFIGINFO"),"");
        }
        if(StringUtil.isEmpty(value) && StringUtil.isNotEmpty(fieldInfo.getStr("FIELD_VALUE"))){
            value=fieldInfo.getStr("FIELD_VALUE","");
        }
        //下拉框
        if("cbb".equals(excelType)){
            String dzCode="";
            if(StringUtil.isNotEmpty(configInfo)) {
                dzCode=configInfo.split(",")[0];
            }
            List<DynaBean> dicLists=jeOfficeVo.getDzValues().get(dzCode);
            List<String> names=new ArrayList<>();
            String displayField=fieldInfo.getStr("EXCEL_CBBFIELD");
            if("LISTDIC".equals(fieldType) && StringUtil.isEmpty(displayField)){
                displayField="text";
            }else if("LISTQUERY".equals(fieldType) && StringUtil.isEmpty(displayField)){
                displayField=fieldCode;
            }
            for(DynaBean dicInfo:dicLists){
                String dicVal=dicInfo.getStr(displayField,"");
                if(StringUtil.isNotEmpty(dicVal)) {
                    names.add(dicVal);
                }
                if("LISTDIC".equals(fieldType) && StringUtil.isNotEmpty(fieldInfo.getStr("FIELD_DZCONFIG"))){
                    String[] dzConfigs=fieldInfo.getStr("FIELD_DZCONFIG").split(",");
                    int flagCs=0;
                    for(String dzConfig:dzConfigs){
                        if(StringUtil.isEmpty(dzConfig)){
                            flagCs++;
                            continue;
                        }
                        String thisField=dzConfig.split("~")[0];
                        String targerField=dzConfig.split("~")[1];
                        if(dynaBean.getStr(thisField).equals(dicInfo.getStr(targerField))){
                            flagCs++;
                        }
                    }
                    if(flagCs==dzConfigs.length){
                        value=dicVal;
                    }
                }
            }
            //增加判断不渲染组件
            if(!"1".equals(fieldInfo.getStr("EXCEL_NORENDER"))){
                doRenderCbb(nowCell,names);
            }
            //TODO 根据names处理为下拉框组件
            nowCell.setCellValue(value);
            //图片
        }else if("img".equals(excelType)){
            String fileKey="";
            if(StringUtil.isNotEmpty(value)){
                if(value.startsWith("[")){
                    JSONArray fileArray=JSONArray.parseArray(value);
                    if(fileArray.size()>0){
                        fileKey=fileArray.getJSONObject(0).getString("path");
                    }
                }else{
                    fileKey=value.split("\\*")[1];
                }
            }
            if(StringUtil.isNotEmpty(fileKey)){
                try {
                    File file = documentInternalRpcService.readFile(fileKey);
                    if (file != null && file.exists()) {
                        doCellImage(nowCell, new FileInputStream(file));
                    }
                }catch (Exception e){

                }
            }
            nowCell.setCellValue("");
            //常规字段
        }else {
            if ("NUMBER".equals(fieldType) && StringUtil.isNotEmpty(value)) {
                int intval = Integer.parseInt(value);
                nowCell.setCellValue(intval);
            } else if ("FLOAT".equals(fieldType) && StringUtil.isNotEmpty(value)) {
                double doubleval = Double.parseDouble(value);
                nowCell.setCellValue(doubleval);
            }else{
                nowCell.setCellValue(value);
            }
        }
    }
    public String getCellStringValue(Cell cell) {
        String cellValue = "";
        if(cell==null) return "";
        switch (cell.getCellType().getCode()) {
            case 1:// 字符串类型
                cellValue = cell.getStringCellValue();
                if (cellValue.trim().equals("") || cellValue.trim().length() <= 0)
                    cellValue = " ";
                break;
            case 0: // 数值类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date d = cell.getDateCellValue();
                    if (d != null) {
                        cellValue = DateUtils.formatDate(d, DateUtils.DAFAULT_DATETIME_FORMAT);
                    } else {
                        cellValue = "";
                    }
                } else {
                    cellValue = String.valueOf(MathExtend.divide(
                            cell.getNumericCellValue(), 1, 2));
//				cellValue=cell.getNumericCellValue()+"";
                    int index=cellValue.lastIndexOf(".0");
                    if(index!=-1 && index==(cellValue.length()-2)){
                        cellValue=cellValue.substring(0,cellValue.length()-2);
                    }
                }
                break;
            case 2: // 公式
                cell.setCellType(CellType.STRING);
//			cellValue = String.valueOf(MathExtend.divide(
//					cell.getNumericCellValue(), 1, 2));
                cellValue=cell.getStringCellValue();
                break;
            case 3:
                cellValue = " ";
                break;
            case 4:
                break;
            case 5:
                break;
            default:
                break;
        }
        return cellValue;
    }
    public void doCellImage(Cell nowCell,FileInputStream fileIo){
        Sheet sheet=nowCell.getSheet();
        Workbook workbook=sheet.getWorkbook();
        JSONObject returnObj=isMergedRegion(nowCell);
        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, returnObj.getInteger("startColumn"),returnObj.getInteger("startRow"),
                returnObj.getInteger("endColumn"), returnObj.getInteger("endRow"));
        Picture picture=sheet.createDrawingPatriarch().createPicture(anchor, workbook.addPicture(IoUtil.readBytes(fileIo,true),Workbook.PICTURE_TYPE_PNG));
        picture.resize(1, 1);
        IoUtil.close(fileIo);
    }
    public void doRenderCbb(Cell nowCell,List<String> names){
        Sheet sheet=nowCell.getSheet();
        String[] ddTexts = ArrayUtils.getArray(names);
        if(sheet instanceof XSSFSheet){
            XSSFDataValidationHelper dvHelper=new XSSFDataValidationHelper((XSSFSheet) sheet);
            XSSFDataValidationConstraint dvConstraint= (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(ddTexts);
            CellRangeAddressList regions = new CellRangeAddressList(nowCell.getRowIndex(), nowCell.getRowIndex(), nowCell.getColumnIndex(), nowCell.getColumnIndex());
            XSSFDataValidation dv= (XSSFDataValidation) dvHelper.createValidation(dvConstraint,regions);
            sheet.addValidationData(dv);
        }else {
            CellRangeAddressList regions = new CellRangeAddressList(nowCell.getRowIndex(), nowCell.getRowIndex(), nowCell.getColumnIndex(), nowCell.getColumnIndex());
            DVConstraint constraint = DVConstraint.createExplicitListConstraint(ddTexts);
            DataValidation data_validation_list = new HSSFDataValidation(regions,
                    constraint);
            sheet.addValidationData(data_validation_list);
        }
    }
    private JSONObject isMergedRegion(Cell nowCell) {
        Sheet sheet=nowCell.getSheet();
        int row =nowCell.getRowIndex();
        int column=nowCell.getColumnIndex();
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    JSONObject returnObj=new JSONObject();
                    returnObj.put("startRow",firstRow);
                    returnObj.put("endRow",lastRow);
                    returnObj.put("startColumn",firstColumn);
                    returnObj.put("endColumn",lastColumn);
                    return returnObj;
                }
            }
        }
        JSONObject returnObj=new JSONObject();
        returnObj.put("startRow",nowCell.getRowIndex());
        returnObj.put("endRow",nowCell.getRowIndex());
        returnObj.put("startColumn",nowCell.getColumnIndex());
        returnObj.put("endColumn",nowCell.getColumnIndex());
        return null;
    }
}
